package org.ping.news;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.ping.Tool.GetData;
import org.ping.Tool.LogUtil;
import org.ping.Tool.NewsUtil;

/**
 * @author PING
 * 这个类是实际的对新闻的操作包括
 * 	1.保存到数据库的操作(接口)
 */
public class MinuteNews implements DatabaseOperation{
	
	private MinuteNews() {}  
    private static final MinuteNews min = new MinuteNews();  
    //静态工厂方法   
    public static MinuteNews getInstance() {  
        return min;
    } 
	
	/**
	 * 保存数据到数据库
	 * 1.数据链接
	 * 2.第2个参数  标题
	 * 3.第3个参数  原文链接
	 * 4.String 数组 0 原文内容 1原文日期 3....图片链接
	 * 6...剩下的都是图片链接
	 */
	@Override
	public void saveData(Connection conn,String linkText,String linkHref,List<String> info ) {
		try {
			PreparedStatement stat = conn.prepareStatement("INSERT INTO news VALUES(null,?,?,?,?,?)");
			stat.setString(1, linkText);
			stat.setString(2, linkHref);
			//数据库数据   num自增   /标题  /标题链接  /正文  /图片  /日期
			
			//0是正文  1是日期  2后面是图片内容
			stat.setString(3,info.get(0)==null?"无正文":info.get(0));
			
			String images = "";
			for(int i = 2;i<info.size();i++) {
				images+=info.get(i)+"#";
			}
			stat.setString(4, images==null?"无图片":images);
			
			stat.setString(5,info.get(1)==null?"无日期":info.get(1));
			
			stat.executeUpdate();
			//System.out.println("储存一条完成!");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				LogUtil.getLogUtil(this).showlog(e);
			}
		}
	}


	//获取新闻
	//数据库链接，最小第几个,偏移量
	/**
	 * 从数据库查询最新的50条数据
	 * 存储到内存
	 */
	@Override
	public void getData(Connection conn) {
		try {
			PreparedStatement stat = conn.prepareStatement("select numbre,title,link,content,date,images from news ORDER BY numbre DESC limit 0,50");
			ResultSet set = stat.executeQuery();
			//先清空上一次推送的数据，然后储存到缓存
			NewsUtil.getNewsUtil().news.clear();
			LogUtil.getLogUtil(this).showlog("开始推送新闻到缓存...");
			while (set.next()) {
				
				String title = set.getString("title");
				String link = set.getString("link");
				String content = set.getString("content");
				String date = set.getString("date");
				String image = set.getString("images");
				int id = set.getInt("numbre");
				//System.out.println(id);
				StorageNew stor = new StorageNew(title, link, content, date, image,id);
				//储存进这个推送类
				NewsUtil.getNewsUtil().news.put(String.valueOf(stor.getId()), stor);
			}
			LogUtil.getLogUtil(this).showlog("推送数据成功--->");;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				LogUtil.getLogUtil(this).showlog(e);
			}
		}
		//推送完成后就清除一部分数据
		deleteData(GetData.getInstance().getConnection());
	}

	//删除多余的数据
	@Override
	public void deleteData(Connection conn) {
		int num = selectData(conn);
		
		int nums = num-200;
		if(num !=0 && num>300) {
			try {
				PreparedStatement stat = conn.prepareStatement("delete from news where numbre < ?");
				stat.setInt(1, nums);
				stat.executeUpdate();
				LogUtil.getLogUtil(this).showlog("清除多余的新闻数据成功！(只留最新200条)---->");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					if(conn!=null) {						
						conn.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
			
		}
	}
	//查询数据
	private int selectData(Connection conn) {
		int num = 0;
		try {
			PreparedStatement stat = conn.prepareStatement("select max(numbre) from news");
			ResultSet set = stat.executeQuery();
			while (set.next()) {
				num = set.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
	}
	
}
